/*
 *
 */
package mwt.xml.xdbforms.dblayer.query.impl.drivermanager;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
import java.util.logging.Level;
import java.util.logging.Logger;
import mwt.xml.xdbforms.dblayer.query.Query;
import mwt.xml.xdbforms.dblayer.query.SQLOperation;
import mwt.xml.xdbforms.dblayer.query.exception.QueryException;
import mwt.xml.xdbforms.dblayer.query.impl.JDBCQueryParameterBuilder;
import mwt.xml.xdbforms.dblayer.query.impl.QueryParameterBuilderDirector;
import mwt.xml.xdbforms.dblayer.query.impl.SQLInsert;
import mwt.xml.xdbforms.dblayer.query.impl.SQLQuery;
import mwt.xml.xdbforms.dblayer.query.impl.SQLUpdate;

/**
 * Class che implementa l'interfaccia Query
 * usando però un il DriverManager per fare
 * delle connessioni
 * @author Gianfranco Murador
 */
public class JDBCQuery implements Query {

    private String username;
    private String password;
    private String url;

    public JDBCQuery(String username, String password, String url, String db) {
        this.username = username;
        this.password = password;
        this.url = url + db;
    }

    /**
     * Nota: richiamo un QueryBuilder per la creazione
     * dinamica di una statement di inserimento
     * @param opertaion Struttura di input per con i dati della query
     * @throws mwt.xml.xdbforms.dblayer.query.exception.QueryException
     */
    public void insert(SQLOperation operation) throws QueryException {

        Connection c = null;
        PreparedStatement ps = null;
        QueryParameterBuilderDirector builderDirector =
                new QueryParameterBuilderDirector(new JDBCQueryParameterBuilder(operation));

        if (!(operation instanceof SQLInsert)) {
            throw new QueryException("opertaion not supported, SQLInsert required");
        }

        try {
            c = DriverManager.getConnection(url, username, password);
            ps = builderDirector.createInsertStatement(c);
            ps.executeUpdate();

        } catch (SQLException ex) {
            throw new QueryException("Insert Error", ex);
        } finally {

            if (c != null) {
                try {
                    c.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }


        }

        builderDirector = null;

    }

    /**
     * Aggiorna una riga della tabella in base alle informazioni passati in input
     * @param operation Struttura di input per con i dati della query
     * @throws mwt.xml.xdbforms.dblayer.query.exception.QueryException
     */
    public void update(SQLOperation operation) throws QueryException {

        Connection c = null;
        PreparedStatement ps = null;
        QueryParameterBuilderDirector builderDirector =
                new QueryParameterBuilderDirector(new JDBCQueryParameterBuilder(operation));

        if (!(operation instanceof SQLUpdate)) {
            throw new QueryException("opertaion not supported, SQLUpdate required");
        }
        try {
            c = DriverManager.getConnection(url, username, password);
            ps = builderDirector.createUpdateStatement(c);
            ps.executeUpdate();

        } catch (SQLException ex) {
            throw new QueryException("Insert Error", ex);
        } finally {

            if (c != null) {
                try {
                    c.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }


        }

        builderDirector = null;

    }

    /**
     * NOTA: Ricordarsi di chiudere sempre il ResultSet quando si
     * richiama questo metodo
     * @param table Nome della tabella
     * @param start riga di inizio
     * @param length numero di righe
     * @return ResultSet insieme risultato
     * @throws mwt.xml.xdbforms.dblayer.query.exception.QueryException
     */
    public Collection read(String table, int start, int length) throws QueryException {

        String sql = "SELECT * FROM " + table + " LIMIT ?,?";
        ResultSet rs = null;
        Connection c = null;
        PreparedStatement ps = null;
        Set set = new HashSet();
        List<String> rowlist = null;



        try {
            c = DriverManager.getConnection(url, username, password);
            ps = c.prepareStatement(sql);
            //     ps.setString(1, table);
            ps.setInt(2, start);
            ps.setInt(3, length);
            rs = ps.executeQuery();

            int numColumns = rs.getMetaData().getColumnCount();

            while (rs.next()) { // Per ogni riga

                //String item = rs.getString(i);
                // per ogni colonna
                rowlist = new ArrayList<String>();
                for (int i = 1; i <= numColumns; i++) {

                    rowlist.add(rs.getString(i));
                }

                set.add(rowlist);

            }






        } catch (SQLException ex) {
            throw new QueryException("Read error", ex);
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();

                }
            }


            if (c != null) {
                try {
                    c.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }

            }

        }
        return set;



    }

    /**
     *
     * NOTA: Ricordarsi di chiudere sempre il ResultSet quando si
     * richiama questo metodo
     * @param table Nome della tabella
     * @return ResultSet insieme risultato
     * @throws mwt.xml.xdbforms.dblayer.query.exception.QueryException
     **/
    public Collection read(String table) throws QueryException {

        String sql = "SELECT * FROM ";
        ResultSet rs = null;
        Connection c = null;
        PreparedStatement ps = null;
        Set set = new HashSet(); // Insieme di righe
        List<String> rowlist = null;
        try {
            c = DriverManager.getConnection(url, username, password);
            ps = c.prepareStatement(sql + table);
            // ps.setString(1, table);
            rs = ps.executeQuery();

            int numColumns = rs.getMetaData().getColumnCount();

            while (rs.next()) { // Per ogni riga

                //String item = rs.getString(i);
                // per ogni colonna
                rowlist = new ArrayList<String>();
                for (int i = 1; i <= numColumns; i++) {

                    rowlist.add(rs.getString(i));
                }

                set.add(rowlist);

            }



        } catch (SQLException ex) {
            throw new QueryException("Read error", ex);
        } finally {

            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException ex) {
                    Logger.getLogger(JDBCQuery.class.getName()).log(Level.SEVERE, null, ex);
                }
            }

            if (c != null) {
                try {
                    c.close();
                } catch (SQLException ex) {
                    Logger.getLogger(JDBCQuery.class.getName()).log(Level.SEVERE, null, ex);
                }

            }

        }
        return set;

    }

    public Collection read(SQLOperation operation) throws QueryException {

        Connection c = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Set set = new HashSet();
        List<String> rowlist = null;

        QueryParameterBuilderDirector builderDirector =
                new QueryParameterBuilderDirector(new JDBCQueryParameterBuilder(operation));

        if (!(operation instanceof SQLQuery)) {
            throw new QueryException("opertaion not supported");
        }

        try {
            c = DriverManager.getConnection(url, username, password);
            ps = builderDirector.createSelectStatement(c);
            rs = ps.executeQuery();

            int numColumns = rs.getMetaData().getColumnCount();

            while (rs.next()) { // Per ogni riga

                //String item = rs.getString(i);
                // per ogni colonna
                rowlist = new ArrayList<String>();
                for (int i = 1; i <= numColumns; i++) {

                    rowlist.add(rs.getString(i));
                }

                set.add(rowlist);

            }



        } catch (SQLException ex) {
            throw new QueryException("Read Error", ex);
        } finally {

            if (c != null) {
                try {
                    c.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();

                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }


        }

        builderDirector = null;
        return set;

    }

    public String getInsertStmt(SQLOperation operation) throws QueryException {
        Connection c = null;
        PreparedStatement ps = null;
        String stmt = null;
        QueryParameterBuilderDirector builderDirector =
                new QueryParameterBuilderDirector(new JDBCQueryParameterBuilder(operation));

        if (!(operation instanceof SQLInsert)) {
            throw new QueryException("opertaion not supported, SQLInsert required");
        }

        try {
            c = DriverManager.getConnection(url, username, password);
            ps = builderDirector.createInsertStatement(c);
            ps.execute();
            stmt = ps.toString();

        } catch (SQLException ex) {
            throw new QueryException("Insert Error", ex);
        } finally {

            if (c != null) {
                try {
                    c.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }


        }

        builderDirector = null;
        return stmt;



    }

    public String getUpdateStmt(SQLOperation operation) throws QueryException {
        Connection c = null;
        PreparedStatement ps = null;
        String stmt = null;
        QueryParameterBuilderDirector builderDirector =
                new QueryParameterBuilderDirector(new JDBCQueryParameterBuilder(operation));

        if (!(operation instanceof SQLUpdate)) {
            throw new QueryException("opertaion not supported, SQLUpdate required");
        }
        try {
            c = DriverManager.getConnection(url, username, password);
            ps = builderDirector.createUpdateStatement(c);
            ps.execute();
            stmt = ps.toString();

        } catch (SQLException ex) {
            throw new QueryException("Insert Error", ex);
        } finally {

            if (c != null) {
                try {
                    c.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }


        }

        builderDirector = null;
        return stmt;

    }
}
